/*
SELECT * FROM Customer_Shipping_Line_Item_Table
	EXEC sp_performance_report_proc '0','0','0','0',1,'xlreport','0'
*/
ALTER  PROCEDURE sp_performance_report_proc

@from_date   	varchar(15),
@to_date     	varchar(15),
@part_id     	varchar(200),
@customer_id 	varchar(200),
@order_type 	int,
@report_type	varchar(30),
@default_prompt	varchar(50)

AS

begin
	create table #PerformanceResults
		([Customer Name] varchar(100), [SalesOrder No] varchar(50),[LineItem No] int,[Part Number] varchar(50),
		 [Actual Requested Date] datetime ,[Actual Requested Date Work Week No] int,[Actual Requested Date Quarter] int,
		 [Latest Requested Date] datetime ,[Latest Requested Date Work Week No] int,[Latest Requested Date Quarter] int,
		 [Schedule Ship Date] datetime , [Schedule Ship Date Work Week No] int,[Schedule Ship Date Quarter] int,
		 [Revised Ship Date] datetime ,[Revised Ship Date Work Week No] int, [Revised Ship Date Quarter] int, 
		 [Actual Ship Date] datetime , [Actual Ship Date Work Week No] int, [Actual Ship Date Quarter] int,
		 [Customer id] int, [SaleOrder Id] int, [Product Id] int,[Act Ship Qty] int, [Requested Qty] int, [Remaining to Ship] int ) 

	DECLARE @SQLSTRING NVARCHAR(4000)

	SET @SQLSTRING = N'insert into #PerformanceResults
		([Customer id], [SalesOrder No],[SaleOrder Id],[LineItem No],[Product Id],
		 [Actual Requested Date],[Actual Requested Date Work Week No],[Actual Requested Date Quarter],
		 [Latest Requested Date],[Latest Requested Date Work Week No],[Latest Requested Date Quarter],
		 [Schedule Ship Date],[Schedule Ship Date Work Week No],[Schedule Ship Date Quarter],
		 [Revised Ship Date],[Revised Ship Date Work Week No], [Revised Ship Date Quarter],
		 [Customer Name],[Part Number],
		 [Actual Ship Date],[Actual Ship Date Work Week No],[Actual Ship Date Quarter],[Act Ship Qty], [Requested Qty] , [Remaining to Ship] )
	SELECT
		SO.CUSTOMER_ID , SALESORDER_NO , SO.SALESORDER_ID , COOL_LINE_ITEM_NO , COOL.PRODUCT_ID , 
		ISNULL(COOL_ACT_REQ_DATE,COOL_REQUESTED_DATE) AS COOL_ACT_REQ_DATE,
		ISNULL(COOL_ACT_REQ_DATE_WW_NO,REQUESTED_DATE_WW_NO) AS COOL_ACT_REQ_DATE_WW_NO,
		ISNULL(COOL_ACT_REQ_DATE_QTR_NO,REQUESTED_DATE_QTR_NO) AS COOL_ACT_REQ_DATE_QTR_NO,
		COOL_REQUESTED_DATE ,REQUESTED_DATE_WW_NO,REQUESTED_DATE_QTR_NO,
		COOL_SCHEDULE_DATE ,SCHEDULE_DATE_WW_NO,SCHEDULE_DATE_QTR_NO,
		COOL_REVISED_DATE,REVISED_DATE_WW_NO,REVISED_DATE_QTR_NO,
		C_NAME,PART_NUMBER,
		(select max(CSLIT_Shipped_Date) from Customer_Shipping_Line_Item_Table where LineItem_ID = COOL.LINEITEM_ID and CSLIT_Status = ''SHIPPED'' ) AS ACT_SHIP_DATE,
		(select top 1 CSLIT_SHIPPED_DATE_WW_NO from Customer_Shipping_Line_Item_Table where LineItem_ID = COOL.LINEITEM_ID and CSLIT_Status = ''SHIPPED'' and CSLIT_Shipped_Date = (select max(CSLIT_Shipped_Date) from Customer_Shipping_Line_Item_Table where LineItem_ID = COOL.LINEITEM_ID and CSLIT_Status = ''SHIPPED'' ) ) AS CSLIT_SHIPPED_DATE_WW_NO,
		(select top 1 CSLIT_SHIPPED_DATE_QTR_NO from Customer_Shipping_Line_Item_Table where LineItem_ID = COOL.LINEITEM_ID and CSLIT_Status = ''SHIPPED'' and CSLIT_Shipped_Date = (select max(CSLIT_Shipped_Date) from Customer_Shipping_Line_Item_Table where LineItem_ID = COOL.LINEITEM_ID and CSLIT_Status = ''SHIPPED'' ) ) AS CSLIT_SHIPPED_DATE_QTR_NO,
		(SELECT sum(CSLIT_QTY_SHIPPED) FROM CUSTOMER_SHIPPING_LINE_ITEM_TABLE CSLIT,Customer_Shipping_Table CST WHERE CSLIT.CustomerShipping_ID = CST.CustomerShipping_ID AND CST_STATUS = ''shipped'' AND CSLIT.LineItem_ID = COOL.LINEITEM_ID ),
		COOL_Qty, (COOL_QTY - ISNULL(( SELECT SUM(CSLIT_QTY_SHIPPED) FROM CUSTOMER_SHIPPING_LINE_ITEM_TABLE WHERE LINEITEM_ID = COOL.LineItem_Id AND CSLIT_STATUS = ''SHIPPED''  ), 0))
	FROM
		 SALES_ORDER SO , CUSTOMER_OPEN_ORDER_LINEITEM COOL, CUSTOMER C, PRODUCT P
	WHERE 
		SO.SALESORDER_ID = COOL.SALESORDER_ID
		AND SO.CUSTOMER_ID = C.CUSTOMER_ID
		AND COOL.PRODUCT_ID = P.PRODUCT_ID '
		
	IF @part_id IS NOT NULL AND @part_id <> '0'
	BEGIN
		SET @SQLSTRING = @SQLSTRING + ' AND COOL.PRODUCT_ID IN  ( SELECT PROCESSSTEP_ID FROM FN_GET_PROCESS_STEPS_IN_STRING ( '''+ @part_id+''''+') )'
	END

	IF @customer_id IS NOT NULL AND @customer_id <> '0'
	BEGIN
		SET @SQLSTRING = @SQLSTRING + ' AND SO.Customer_ID IN  ( SELECT PROCESSSTEP_ID FROM FN_GET_PROCESS_STEPS_IN_STRING ( '''+ @customer_id+''''+') )'
	END

	/*IF  @order_type > 0
	BEGIN
		SET @SQLSTRING = @SQLSTRING + ' AND SO.SO_Is_Sample = '''+ CONVERT(VARCHAR,@order_type)+''''
	END
	ELSE IF @order_type = 0
	BEGIN
		SET @SQLSTRING = @SQLSTRING + ' AND SO.SO_Is_Sample = '''+ CONVERT(VARCHAR,@order_type)+''''
	END*/

	IF ( @order_type IS NOT NULL AND @order_type  <> 2 )
	BEGIN
		SET @SQLSTRING = @SQLSTRING + ' AND so_is_consignment = 1 AND SO.SO_Is_Sample = '''+ CONVERT(VARCHAR,@order_type)+''''
	END
	ELSE IF ( @order_type IS NOT NULL AND @order_type = 2 )
	BEGIN
		SET @SQLSTRING = @SQLSTRING + ' and so_is_consignment = 0 '
	END

	--print'@SQLSTRING'+@SQLSTRING
	EXEC (@SQLSTRING)

	if ( @from_date = '0' )
	begin
		select @from_date = convert(varchar,min(CSLIT_Shipped_Date),101) from Customer_Shipping_Line_Item_Table
	end
	if ( @to_date = '0' )
	begin
		select @to_date = convert(varchar,max(CSLIT_Shipped_Date),101) from Customer_Shipping_Line_Item_Table
	end

	if @report_type = 'xlreport'
	begin
		select 
			[Customer Name],[SalesOrder No],[LineItem No],[Part Number],[Act Ship Qty],
		 	[Actual Requested Date],[Actual Requested Date Work Week No],[Actual Requested Date Quarter],
			[Latest Requested Date],[Latest Requested Date Work Week No],[Latest Requested Date Quarter],
			[Schedule Ship Date],[Schedule Ship Date Work Week No],[Schedule Ship Date Quarter], 
		 	[Revised Ship Date],[Revised Ship Date Work Week No], [Revised Ship Date Quarter],
			[Actual Ship Date],[Actual Ship Date Work Week No],[Actual Ship Date Quarter],
			[Requested Qty], [Remaining to Ship]
		from 
			#PerformanceResults 
		where 
			([Actual Ship Date] between @from_date and @to_date) or [Actual Ship Date] is null
		order by
			[Customer Name],[SalesOrder No],[LineItem No]
	end
	else
	begin
		select 
			[Customer Name],[SalesOrder No],[LineItem No],[Part Number],[Act Ship Qty],
		 	[Actual Requested Date],[Latest Requested Date],[Schedule Ship Date], 
		 	[Revised Ship Date],[Actual Ship Date],[Customer id],[SaleOrder Id],[Product Id]
		from 
			#PerformanceResults 
		where 
			([Actual Ship Date] between @from_date and @to_date) or [Actual Ship Date] is null
		order by
			[Customer Name],[SalesOrder No],[LineItem No]
	end
end